package de.g18.llmaa.logic.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import de.g18.llmaa.logic.vos.VOKey;
import de.g18.llmaa.logic.vos.VOKey_Type;
import de.g18.llmaa.logic.vos.VOPerson;
import de.g18.llmaa.logic.vos.VOPerson_Schueler;
import de.g18.llmaa.logic.vos.VOProduct_Key;
import de.g18.llmaa.logic.vos.VOSchulKlasse;

public class HSQLDBConnect {

	public static HSQLDBConnect instanze;

	Connection conn; // our connnection to the db - presist for life of program

	// we dont want this garbage collected until we are done
	private HSQLDBConnect() { // note more general exception
		try {
			Class.forName("org.hsqldb.jdbcDriver");

			conn = DriverManager.getConnection(
					"jdbc:hsqldb:file:database8/llmaa", // filenames
					"sa", // username
					""); // password
		} catch (Exception e) {
			e.printStackTrace();
			JOptionPane
					.showMessageDialog(
							new JFrame(),
							"Die Datenbank ist aktuell in Benutzung \n bitte schließen sie andere Anwendungen \n welche die DB Benutzen könnten ",
							"Datenbank in Benutzung", JOptionPane.ERROR_MESSAGE);

		}
	}

	public static HSQLDBConnect getInstanze() {
		if (instanze == null)
			instanze = new HSQLDBConnect();
		return instanze;
	}

	public void shutdown() throws SQLException {

		Statement st = conn.createStatement();

		// db writes out to files and performs clean shuts down
		// otherwise there will be an unclean shutdown
		// when program ends
		st.execute("SHUTDOWN");
		conn.close(); // if there are no other open connection
	}

	public boolean saveVOKey(VOKey key) {

		SimpleDateFormat formater = new SimpleDateFormat();
		formater.applyPattern("yyyy-mm-dd");
		String datum = "";
		if (key.getKey_ClaimedDate() != null) {
			datum = formater.format(key.getKey_ClaimedDate());
		}
		String statement = "Insert Into VOKey(key_Id,key_ClaimedDate,key_Serial,key_Product_id,key_Type_id) VALUES('"
				+ key.getKey_Id()
				+ "',"
				+ "'"
				+ datum
				+ "','"
				+ key.getKey_Serial()
				+ "',"
				+ key.getKey_Product().getId()
				+ "," + key.getKey_Type().getId() + ")";

		try {
			update(statement);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public void saveVOPoduct_KeyList(ArrayList<VOProduct_Key> listProducts) {
		for (VOProduct_Key voProductKey : listProducts) {
			try {
				saveVOPoduct_Key(voProductKey);
				// selectVOPoduct_Key(voProductKey.getProduct_Name());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				if (e.getErrorCode() == -104) {

					System.out.println(e.getErrorCode());
				}
				// e.printStackTrace();
			}
			VOProduct_Key product = selectVOPoduct_Key(voProductKey
					.getProduct_Name());
			for (VOKey key : voProductKey.getList_Key()) {
				key.setKey_Product(product);
				saveVOKeyType(key.getKey_Type().getType_Name());
				key.setKey_Type(selectType(key.getKey_Type().getType_Name()));
				saveVOKey(key);
				System.out.println("hallo");
			}
		}

	}

	private void saveVOKeyType(String typeName) {
		String statement = "Insert Into VOKey_Type (type_Name) Values('"
				+ typeName + "')";
		try {
			update(statement);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public VOProduct_Key selectVOPoduct_Key(String productName) {
		String statement = "Select * from VOProduct_Key  where product_Name = '"
				+ productName + "'";

		try {
			ResultSet rs = query(statement);

			// ResultSetMetaData meta = rs.getMetaData();

			for (; rs.next();) {
				VOProduct_Key type = parseRSfromVOProduct_Key(rs);
				return type;

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	private VOProduct_Key parseRSfromVOProduct_Key(ResultSet rs) {

		try {
			VOProduct_Key tempKey = new VOProduct_Key();

			tempKey.setId(Integer.parseInt(rs.getObject(1).toString()));
			tempKey.setProduct_Name(rs.getObject(2).toString());

			tempKey.setList_Key(selectLizenzForProduct(tempKey));

			return tempKey;
			// }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

	private ArrayList<VOKey> selectLizenzForProduct(VOProduct_Key productKey) {
		String statement = "Select * From VOKey where key_Product_id = "
				+ productKey.getId();
		System.out.println(statement);
		ArrayList<VOKey> tempList = new ArrayList<VOKey>();
		try {
			ResultSet rs = query(statement);

			// ResultSetMetaData meta = rs.getMetaData();

			for (; rs.next();) {
				System.out.println("da");
				VOKey tempKey = parseRSfromVOKey(rs);
				tempKey.setKey_Product(productKey);
				tempList.add(tempKey);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return tempList;
	}

	private VOKey parseRSfromVOKey(ResultSet rs) {
		VOKey key = new VOKey();
		try {
			key.setKey_Id(rs.getInt(1));
			key.setKey_ClaimedDate(rs.getDate(2));
			key.setKey_Type(selectType(rs.getInt(5)));
			key.setKey_Serial(rs.getString(3));
			System.out.println(rs.getString(6));
			if (rs.getString(6) != null) {
				key.setKey_User(selectPerson(rs.getString(6)));
			}
			key.setKey_InternId(rs.getInt(7));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return key;
	}

	private VOKey_Type selectType(int typeId) {
		String statement = "Select * From VOKey_Type where id =" + typeId;
		try {
			ResultSet rs = query(statement);

			// ResultSetMetaData meta = rs.getMetaData();

			for (; rs.next();) {
				VOKey_Type tempKey = parseRSfromVOKeyType(rs);
				return tempKey;

			}
		} catch (SQLException e) {
			// TODO: handle exception
		}

		return null;
	}

	public ArrayList<VOProduct_Key> selectAllProducts() {
		ArrayList<VOProduct_Key> tempList = new ArrayList<VOProduct_Key>();
		String statement = "Select * From VOProduct_Key ";
		try {
			ResultSet rs = query(statement);
			for (; rs.next();) {
				tempList.add(parseRSfromVOProduct_Key(rs));
			}
		} catch (SQLException e) {

			// TODO: handle exception
		}
		return tempList;
	}

	private VOKey_Type selectType(String typeName) {
		String statement = "Select * From VOKey_Type where type_Name = '"
				+ typeName + "'";
		try {
			ResultSet rs = query(statement);

			// ResultSetMetaData meta = rs.getMetaData();
			// System.out.println(rs.getRow());
			for (; rs.next();) {
				VOKey_Type tempKey = parseRSfromVOKeyType(rs);
				return tempKey;

			}
			System.out.println("das dasdasdas");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	private VOKey_Type parseRSfromVOKeyType(ResultSet rs) {
		VOKey_Type tempType = new VOKey_Type();
		try {
			tempType.setId(rs.getInt(2));
			tempType.setType_Name(rs.getString(1));

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tempType;
	}

	public void saveVOPoduct_Key(VOProduct_Key voProductKey)
			throws SQLException {

		String statement = "Insert Into VOProduct_Key(product_Name) VALUES('"
				+ voProductKey.getProduct_Name() + "')";

		update(statement);

	}

	public void initDatabase() {
		try {
			// update("DROP TABLE VOKey2");
			update("CREATE TABLE VOKey ( "
					+ "  key_Id INTEGER ,"
					+ "  key_ClaimedDate datetime,"
					+ "  key_Serial varchar(255) ,"
					+ "  key_Product_id INTEGER,"
					+ "  key_Type_id INTEGER,"
					+ "  key_User varchar(255),"
					+ "  key_InternId INTEGER IDENTITY,"
					+ "  "
					+ " CONSTRAINT key_Serial2 UNIQUE(key_Serial,key_Product_id)"
					+ "  )");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			update("CREATE TABLE VOKey_Type ("
					+ "type_Name varchar(255) NOT NULL,"
					+ "id INTEGER IDENTITY ," + " PRIMARY KEY (id),"
					+ " CONSTRAINT type_Name UNIQUE(type_Name)" + "  )");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			update("CREATE TABLE  VOProduct_Key (" + "id INTEGER IDENTITY,"
					+ "product_Name varchar(255) DEFAULT NULL,"
					+ "PRIMARY KEY (id),"
					+ " CONSTRAINT product_Name UNIQUE(product_Name)" + "  )");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			update("CREATE TABLE  VO_SchulKlassen (" + "kid INTEGER IDENTITY,"
					+ "klassenName varchar(255) DEFAULT NULL,"
					+ "PRIMARY KEY (kid),"
					+ " CONSTRAINT klassenName UNIQUE(klassenName)" + "  )");

			update("Insert Into VO_SchulKlassen (kid,klassenName) Values(1,'dieKlasse')");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			update("CREATE TABLE  VO_Person (" + "pid INTEGER IDENTITY,"
					+ "vorname varchar(255) DEFAULT NULL,"
					+ "nachname varchar(255) DEFAULT NULL,"
					+ "PRIMARY KEY (pid)" + "  )");

			update("Insert Into VO_Person (pid,vorname,nachname) Values(1,'Volker','Lürsen')");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			update("CREATE TABLE  VO_Schueler (" + "mid INTEGER IDENTITY,"
					+ "vorname varchar(255) DEFAULT NULL,"
					+ "nachname varchar(255) DEFAULT NULL," + "kid INTEGER,"
					+ "dabeiSeit Date DEFAULT NULL,"
					+ "dabeiBis Date DEFAULT NULL," + "PRIMARY KEY (mid)"
					+ "  )");

			update("Insert Into VO_Schueler (mid,vorname,nachname,kid) Values(1,'Malte','Finnern',1)");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public synchronized ResultSet query(String expression) throws SQLException {

		Statement st = null;
		ResultSet rs = null;

		st = conn.createStatement(); // statement objects can be reused with

		// repeated calls to execute but we
		// choose to make a new one each time
		rs = st.executeQuery(expression); // run the query

		// do something with the result set.
		// dump(rs);
		st.close(); // NOTE!! if you close a statement the associated ResultSet
		// is
		return rs;

	}

	// use for SQL commands CREATE, DROP, INSERT and UPDATE
	public synchronized void update(String expression) throws SQLException {

		Statement st = null;
		System.out.println(expression);
		st = conn.createStatement(); // statements

		int i = st.executeUpdate(expression); // run the query

		if (i == -1) {
			System.out.println("db error : " + expression);
		}

		st.close();
	} // void update()

	public ArrayList<VOSchulKlasse> selectAllSchulKlassen() {
		String statement = "Select * From VO_SchulKlassen";
		ArrayList<VOSchulKlasse> listKlassen = new ArrayList<VOSchulKlasse>();
		try {
			ResultSet rs = query(statement);

			ResultSetMetaData meta = rs.getMetaData();
			// System.out.println(rs.getRow());
			for (; rs.next();) {
				VOSchulKlasse tempKlasse = parseRSfromSchulKlasse(rs);
				listKlassen.add(tempKlasse);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listKlassen;
	}

	private VOSchulKlasse parseRSfromSchulKlasse(ResultSet rs) {
		VOSchulKlasse tempKlasse = new VOSchulKlasse();
		try {
			tempKlasse.setKlassenId(rs.getInt(1));
			tempKlasse.setKlassenName(rs.getString(2));

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tempKlasse;
	}

	public ArrayList<VOPerson_Schueler> selectSchueler(String vorName,
			String nachName, int klassenId) {
		String statement = "Select * From VO_Schueler ";

		if (!vorName.equals("") || !nachName.equals("") || klassenId != 0) {
			statement = statement + " Where ";
		}

		if (!vorName.equals("")) {
			statement = statement + " vorName like '" + vorName + "'";
		}
		if (!nachName.equals("")) {
			if (!vorName.equals("")) {
				statement = statement + " AND ";
			}
			statement = statement + " nachName like '" + nachName + "'";

		}
		if (klassenId != 0) {
			if (!vorName.equals("") || !nachName.equals("")) {
				statement = statement + " AND ";
			}
			statement = statement + " kid = " + klassenId;
		}
		System.out.println(statement);

		ArrayList<VOPerson_Schueler> listSchueler = new ArrayList<VOPerson_Schueler>();
		try {
			ResultSet rs = query(statement);

			ResultSetMetaData meta = rs.getMetaData();
			// System.out.println(rs.getRow());
			for (; rs.next();) {
				VOPerson_Schueler tempSchueler = parseRSfromSchueler(rs);
				listSchueler.add(tempSchueler);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listSchueler;
	}

	private VOPerson_Schueler parseRSfromSchueler(ResultSet rs) {
		VOPerson_Schueler tempSchueler = new VOPerson_Schueler();
		try {
			tempSchueler.setPersonID(rs.getInt(1));
			tempSchueler.setVorName(rs.getString(2));
			tempSchueler.setNachName(rs.getString(3));
			// System.out.println(rs.getString(4));
			tempSchueler.setSchuelerSeit(rs.getDate(5));
			tempSchueler.setSchuelerBis(rs.getDate(6));
			try {
				tempSchueler.setKlasse(selectSchulKlasse(rs.getInt(4)));
			} catch (Exception e) {
				// TODO: handle exception
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tempSchueler;
	}

	private VOSchulKlasse selectSchulKlasse(int klassenId) {
		String statement = "Select * From VO_SchulKlassen where kid = "
				+ klassenId;

		try {
			ResultSet rs = query(statement);

			ResultSetMetaData meta = rs.getMetaData();
			// System.out.println(rs.getRow());
			for (; rs.next();) {
				VOSchulKlasse tempKlasse = parseRSfromSchulKlasse(rs);

				return tempKlasse;

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private VOPerson selectPerson(String id) {
		String statement = "";
		if (id.startsWith("L")) {
			statement = "Select * From VO_Person Where pid = "
					+ id.substring(1, id.length());
			try {
				ResultSet rs = query(statement);

				ResultSetMetaData meta = rs.getMetaData();
				// System.out.println(rs.getRow());
				for (; rs.next();) {
					VOPerson tempKlasse = parseRSfromPerson(rs);

					return tempKlasse;

				}

			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		if (id.startsWith("S")) {
			statement = "Select * From VO_Schueler Where mid =  "
					+ id.substring(1, id.length());
			try {
				ResultSet rs = query(statement);

				ResultSetMetaData meta = rs.getMetaData();
				// System.out.println(rs.getRow());
				for (; rs.next();) {
					VOPerson_Schueler tempKlasse = parseRSfromSchueler(rs);

					return tempKlasse;

				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	public ArrayList<VOPerson> selectPerson(String vorName, String nachName) {
		String statement = "Select * From VO_Person ";

		if (!vorName.equals("") || !nachName.equals("")) {
			statement = statement + " Where ";
		}

		if (!vorName.equals("")) {
			statement = statement + " vorName like '" + vorName + "'";
		}
		if (!nachName.equals("")) {
			if (!vorName.equals("")) {
				statement = statement + " AND ";
			}
			statement = statement + " nachName like '" + nachName + "'";

		}

		System.out.println(statement);

		ArrayList<VOPerson> listSchueler = new ArrayList<VOPerson>();
		try {
			ResultSet rs = query(statement);

			ResultSetMetaData meta = rs.getMetaData();
			// System.out.println(rs.getRow());
			for (; rs.next();) {
				VOPerson tempSchueler = parseRSfromPerson(rs);
				listSchueler.add(tempSchueler);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listSchueler;

	}

	private VOPerson parseRSfromPerson(ResultSet rs) {
		VOPerson tempPerson = new VOPerson();
		try {
			tempPerson.setPersonID(rs.getInt(1));
			tempPerson.setVorName(rs.getString(2));
			tempPerson.setNachName(rs.getString(3));
			// System.out.println(rs.getString(4));

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tempPerson;
	}

	public boolean checkPersonForKey(VOKey correntKey, VOPerson person) {

		String zusatz = "L";

		if (person instanceof VOPerson_Schueler) {
			zusatz = "S";
		}
		String statement = "Select Count(*) from VOKey where key_Product_id = "
				+ correntKey.getKey_Product().getId() + " AND key_User like '"
				+ zusatz + person.getPersonID() + "'";
		try {
			ResultSet rs = query(statement);

			for (; rs.next();) {
				if (rs.getInt(1) == 0) {
					return false;
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return true;
	}

	public void updateKeyUser(VOKey correntKey, VOPerson person) {
		String zusatz = "L";
		if (person instanceof VOPerson_Schueler) {
			zusatz = "S";
		}
		String statement = "update VOKey Set key_User = '" + zusatz
				+ person.getPersonID() + "' Where key_InternId = "
				+ correntKey.getKey_InternID();
		try {
			update(statement);
			correntKey.setKey_User(person);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		;

	}

} // class Testdb

